﻿using Flare.DB.CeEntity;
using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Linq;

namespace Flare.DB
{
    class general_rooms
    {
        public IEnumerable<general_room> Index(int? id_department = null)
        {
            return Source.general_room().Where(m => m.id_department == id_department);
        }

        public IEnumerable<general_room> Index2(int? id_department = null)
        {

            return Source.general_room().Where(m => m.id_department == id_department || m.id_department == null);
        }

        public void Create(general_room data)
        {
            data.id_room = 0;

            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(
                        string.Format("INSERT INTO [general_room]([id_department],[room]) VALUES ({0},'{1}')",
                        data.id_department, data.room), connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время добавления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Edit(general_room data)
        {
            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(
                        string.Format("UPDATE [general_room] SET [id_department] = {1}, [room] = '{2}' WHERE [id_room] = {0}",
                        data.id_room, data.id_department, data.room), connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время обновления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Delete(int id_room)
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(string.Format("DELETE FROM [general_room] WHERE [id_room] = {0}", id_room), connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время удаления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public int Count()
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"SELECT COUNT(*) FROM [general_room]", connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }
                    int count = 0;
                    try
                    {
                        using (SqlCeDataReader reader = command.ExecuteReader())
                        {
                            reader.Read();
                            count = Convert.ToInt16(reader[0]);
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText);
                        glob.kernel.Panic(glob.PanicType.sql, ex);
                    }
                    return count;
                }
            }
        }
    }
}
